package cn.com.fssg.platform.base.handler.impl.cl.tongji;

import cn.com.fssg.platform.base.handler.InterfaceHandler;
import cn.com.fssg.platform.biz.BizUtil;
import com.alibaba.fastjson.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class ClCjrNumCounterInterfaceHandler implements InterfaceHandler {
//    private NamedParameterJdbcTemplate jdbcTemplate = BizUtil.cur().tenantMainJdbcTemplate();
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    private SimpleDateFormat yearFormat = new SimpleDateFormat("yyyy");

    @Override
    public Object run(HttpServletRequest request, JSONObject jsonObject) throws Exception {
        String countSql = "SELECT count(1) as total, count(cjr_num) as has_cjr_card FROM cl_apply_xunlian";
        Map<String, Object> paraMap = new HashMap<String, Object>();
        Map<String, Object> resultMap = new HashMap<>();
        Map<String, Object> countMap;
        try{
            countMap = jdbcTemplate.queryForMap(countSql, paraMap);
        } catch (Exception e) {
            return "获取持证人数异常：" + e.getMessage();
        }
            int currentYear = Integer.parseInt(yearFormat.format(new Date()));
            String start = currentYear + "-01-01";
            String end = currentYear + "-12-31";
            String querySql = "SELECT " +
                    "    TO_CHAR(apply_time, 'YYYY-MM') AS month," +
                    "    COUNT(*) AS total_count," +
                    "    COUNT(cjr_num) AS has_cjr_card," +
                    "    COUNT(*) - COUNT(cjr_num) AS has_no_card" +
                    " FROM cl_apply_xunlian " +
                    " WHERE apply_time >= '%s' AND apply_time <= '%s' " +
                    " GROUP BY TO_CHAR(apply_time, 'YYYY-MM') " +
                    " ORDER BY month";

            querySql = String.format(querySql, start, end);
            List<Map<String, Object>> list;
            try {
                list = jdbcTemplate.queryForList(querySql, paraMap);

            } catch (Exception e) {
                return "获取全年持证人数异常：" + e.getMessage();
            }
            resultMap.put("numCount", countMap);
            resultMap.put("monthCount", list);

        return resultMap;
    }

    @Override
    public void run(HttpServletRequest request, HttpServletResponse response, JSONObject jsonObject) throws Exception {
        InterfaceHandler.super.run(request, response, jsonObject);
    }
}
